/*
Author Matthew G. Monteleone mgm@aut-aut.hr www.aut-aut.net
This is an example SP to get data from a queue stats database and return URLs to
be able to listen to calls from a GAPPS spreadsheet.

http://www.asternic.biz/ for more information on Queue Stats

Mostly illustrative in nature, but can be usefull by itself.

For this to work, you need to have a copy of your asterisk cdrdb on the same
database server as your queue stats database.

To be able to get to your recording files, you will need to change your asterisk
set up to save recording files by year/month/day folder structure.

*/
drop procedure inbound_get_recs_basic_by_dates;
go
CREATE PROCEDURE inbound_get_recs_basic_by_dates (IN in_dateFrom date, in_dateTo date)
NOT DETERMINISTIC
CONTAINS SQL
BEGIN
select distinct
qs.datetime,
concat("http://[address.of.your.fileshare]/",year(qs.datetime),"/",date_format(qs.datetime,"%m"),"/",date_format(qs.datetime,"%d"),"/",qs.uniqueid,".mp3") as Link,
cdr.uniqueid 
,cdr.clid
,cdr.dst
,cdr.duration
,cdr.billsec
,cdr.disposition
,qn.queue

from qstats_pbx_004.queue_stats qs
    left join qstats_pbx_004.qagent qa on qs.qagent = qa.agent_id
    left join qstats_pbx_004.qname qn on qs.qname = qn.qname_id
    left  join cdrdb_replica.cdrs cdr on qs.uniqueid = cdr.uniqueid and cdr.id_pbx = 4
where qevent = 10 and qn.queue in (4031,4032,4033,4034,4035,4036) //put the queues you want to show here
and qs.datetime between date(in_dateFrom) and date_add(date(in_dateTo), interval 1 day)
order by 1;
END
GO
